package com.flong.modules.service;

import java.math.BigDecimal;
import java.util.List;

import com.flong.modules.BaseService;
import com.flong.modules.model.oracle.UserColComments;
import com.flong.modules.model.oracle.UserConsColumns;
import com.flong.modules.model.oracle.UserTabColumns;
import com.flong.modules.model.oracle.UserTabComments;
import com.flong.modules.model.oracle.UserTables;
import com.flong.modules.model.oracle.Views;
import com.flong.modules.utils.IoUtils;
import com.flong.modules.utils.ObjectUtil;

/**
 * 
 * @Description SQL脚本追加
 * @ClassName	SqlScriptService
 * @Date		2017年3月1日 上午10:06:47
 * @Author		liangjilong
 * @Copyright (c) All Right Reserved , 2017.
 */
public class SqlScriptService extends BaseService{
	
	
	
	 /**
	  * 
 	  * @Description TODO
 	  * @Author		liangjilong
 	  * @Date		2017年3月1日 上午10:27:53
 	  * @param tableName
 	  * @return 		参数
 	  * @return 		String 返回类型
	 * @throws Exception 
	  */
	public static String createTableSQLScript(String tableName) throws Exception{
		//1、判断是否存在这个表
		int count = TableService.getCountByTableName(tableName);
		//2、组装execute immediate 'create table ...'
		if(count > 0){
			StringBuffer buffer =  new StringBuffer();
			//String path = SqlScriptService.class.getClassLoader().getResource("config/table.sql").getPath();
			//result = IoUtils.reader(path).replaceAll("//#placeholder#", tableName);//读取sql模板文件
			//3、获取表的字段名称，并且获取约束是否为空(Null or Not null)
			List<UserTabColumns> userTabColumnList = TableColumnsService.getUserTabColumns(tableName);
			List<UserTabComments> userTabCommentList = UserTabCommentsService.getUserTabCommentsByName(tableName);
			List<UserColComments> userColCommentList = UserColCommentsService.getUserColCommentsByName(tableName);
			UserConsColumns userConsColumns = UserConsColumnsService.getUserConsColumnsByName(tableName);
			
			//----组装表字段列名
			int size = userTabColumnList.size()-1;//计算最后一个表列名
			
			buffer.append("--创建"+tableName+"表").append(ENTER);
			buffer.append("declare").append(ENTER);
			buffer.append(TAB).append("iCnt number := 0;").append(ENTER);
			buffer.append(" begin ").append(ENTER);
			buffer.append(TAB).append("select count(*) into iCnt from user_tables where lower(table_name) = lower('"+tableName+"');").append(ENTER); 
			buffer.append(" if iCnt = 0 then ").append(ENTER);  
			buffer.append(TAB).append("execute immediate 'create table "+tableName+"(").append(ENTER);
			
			for (int i = 0; i < userTabColumnList.size(); i++) {
				UserTabColumns userTabColumns = userTabColumnList.get(i);
				UserColComments userColComments = userColCommentList.get(i);
				String column_name1 = userColComments.getColumn_name();//列名
				String column_name2 = userTabColumns.getColumn_name();//列名
				String nullable = userTabColumns.getNullable();//是否允许为空
				String data_type = userTabColumns.getData_type();//数据库表的字段类型.
				
				BigDecimal data_length = userTabColumns.getData_length();
				BigDecimal data_precision = userTabColumns.getData_precision();
				BigDecimal data_scale = userTabColumns.getData_scale();
				
				
				if(ObjectUtil.isNotEmpty(column_name1) &&  ObjectUtil.isNotEmpty(column_name2) && column_name1.equalsIgnoreCase(column_name2)){
					
					//判断表字段类型是否为NULL或NOT NULL
					if(ObjectUtil.isNotEmpty(nullable) && !"N".equalsIgnoreCase(nullable)){
						if(size==i){
							if("VARCHAR2".equalsIgnoreCase(data_type) || "NVARCHAR2".equalsIgnoreCase(data_type) ||"CHAR".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type+"("+data_length+")" ).append(ENTER);
							}else if("NUMBER".equalsIgnoreCase(data_type)){
								if(data_precision!= null && data_scale!=null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+","+data_scale+") ").append(ENTER);
								}else if(data_precision!= null && data_scale==null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+",0) ").append(ENTER);
								}else{
									buffer.append(TAB+column_name1+TAB+data_type).append(ENTER);
								}
							}else if(("DATE".equalsIgnoreCase(data_type))||(data_type!=null && data_type.contains("TIMESTAMP")) ){
								buffer.append(TAB+column_name1+TAB+data_type).append(ENTER);
							}else if("LONG".equalsIgnoreCase(data_type) ||"NCLOB".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type).append(ENTER);
							}
						}else{
							//为空但是有逗号分开
							if("VARCHAR2".equalsIgnoreCase(data_type) || "NVARCHAR2".equalsIgnoreCase(data_type) ||"CHAR".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type+"("+data_length+")" ).append(COMMA).append(ENTER);
							}else if("NUMBER".equalsIgnoreCase(data_type)){
								if(data_precision!= null && data_scale!=null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+","+data_scale+") ").append(COMMA).append(ENTER);
								}else if(data_precision!= null && data_scale==null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+",0) ").append(COMMA).append(ENTER);
								}else{
									buffer.append(TAB+column_name1+TAB+data_type).append(COMMA).append(ENTER);
								}
							}else if(("DATE".equalsIgnoreCase(data_type))||(data_type!=null && data_type.contains("TIMESTAMP")) ){
								buffer.append(TAB+column_name1+TAB+data_type).append(COMMA).append(ENTER);
							}else if("LONG".equalsIgnoreCase(data_type) ||"NCLOB".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type).append(COMMA).append(ENTER);
							}
						}
					}else{
						if(size==i){
							if("VARCHAR2".equalsIgnoreCase(data_type) || "NVARCHAR2".equalsIgnoreCase(data_type) ||"CHAR".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type+"("+data_length+")  not null "  ).append(ENTER);
							}else if("NUMBER".equalsIgnoreCase(data_type)){
								if(data_precision!= null && data_scale!=null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+","+data_scale+")  not null").append(ENTER);
								}else if(data_precision!= null && data_scale==null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+",0)  not null ").append(ENTER);
								}else{
									buffer.append(TAB+column_name1+TAB+data_type+"").append(ENTER);
								}
							}else if(("DATE".equalsIgnoreCase(data_type))||(data_type!=null && data_type.contains("TIMESTAMP")) ){
								buffer.append(TAB+column_name1+TAB+data_type).append(ENTER);
							}else if("LONG".equalsIgnoreCase(data_type) ||"NCLOB".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type).append(ENTER);
							}
							
						}else{
							if("VARCHAR2".equalsIgnoreCase(data_type) || "NVARCHAR2".equalsIgnoreCase(data_type) ||"CHAR".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type+"("+data_length+")  not null "  ).append(COMMA).append(ENTER);
							}else if("NUMBER".equalsIgnoreCase(data_type)){
								if(data_precision!= null && data_scale!=null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+","+data_scale+")  not null").append(COMMA).append(ENTER);
								}else if(data_precision!= null && data_scale==null){
									buffer.append(TAB+column_name1+TAB+data_type+"("+data_precision+",0)  not null ").append(COMMA).append(ENTER);
								}else{
									buffer.append(TAB+column_name1+TAB+data_type+" ").append(COMMA).append(ENTER);
								}
							}else if(("DATE".equalsIgnoreCase(data_type))||(data_type!=null && data_type.contains("TIMESTAMP")) ){
								buffer.append(TAB+column_name1+TAB+data_type).append(COMMA).append(ENTER);
							}else if("LONG".equalsIgnoreCase(data_type) ||"NCLOB".equalsIgnoreCase(data_type)){
								buffer.append(TAB+column_name1+TAB+data_type).append(COMMA).append(ENTER);
							}
						}
					} 
				}
			}
			buffer.append(TAB).append(")';").append(ENTER);
			//4、获取表名给予表加备注.（有就加，无默认表描述）
			if(ObjectUtil.isNotEmpty(userTabCommentList) && ObjectUtil.isNotEmpty(userTabCommentList.get(0))){
				String comments = userTabCommentList.get(0).getComments();
		    	comments = (comments==null?" this is "+tableName+" tableName ":comments);
				buffer.append(TAB).append("execute immediate 'comment on table "+tableName +" is ''"+comments+"'''").append(";").append(ENTER);
			}
			//5、添加主键执行SQL脚本
			if(ObjectUtil.isNotEmpty(userConsColumns) && ObjectUtil.isNotEmpty(userConsColumns.getConstraint_name())){
				buffer.append(TAB).append("execute immediate 'alter table "+tableName).append(" add constraint "+userConsColumns.getConstraint_name()+" ");
				buffer.append("primary key ("+userConsColumns.getColumn_name()+")' ;").append(ENTER);
			}
			//6、获取表字段名给予字段加备注（有就加，无默认字段描述）
		    for(UserColComments col: userColCommentList){
		    	String comments = col.getComments();
		    	String column_name = col.getColumn_name();
		    	comments = (comments==null?" this is "+column_name+" columnName ":comments);
		    	buffer.append(TAB).append("execute immediate 'comment on column "+tableName+"."+col.getColumn_name()+" is ''"+comments+"'''").append(";").append(ENTER);
		    }
		    
		    //---end if
		    buffer.append(TAB).append("end if;").append(ENTER);
		    buffer.append(" end;").append(ENTER);
		    buffer.append("/").append(ENTER);
			result = buffer.toString();
		}
		return result;
	}

	
	public static String createTableSQLScript() throws Exception{
		List<UserTables> tables = TableService.getTables();
		int i =0;
		for(UserTables table : tables){
			String table_name = table.getTable_name();
			result+= createTableSQLScript(table_name);
			i++;
		}
		System.out.println(i);
		return result;
	}
	 
	/**
	 * 
	 * @Description TODO
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午10:28:06
	 * @param viewName
	 * @return 		参数
	 * @return 		String 返回类型
	 * @throws Exception 
	 */
	public static String createViewSQLScript(String viewName) throws Exception{
		//1、判断是否存在这个视图
		int count = ViewService.getCountViewName(viewName);
		System.out.println(count);
		//2、组装execute immediate 'create View ...'
	 	if(count > 0){
	 		String path = SqlScriptService.class.getClassLoader().getResource("config/view.sql").getPath();
			result = IoUtils.reader(path).replaceAll("//#placeholder#", viewName);//读取sql模板文件
			//这个viewList和views对象不用判断是否为空，因为count>0
			List<Views> viewList = ViewService.getViewByName(viewName);
			Views views = viewList.get(0);
			String text = views.getText();
			result = result.replaceAll("#executePlaceholderStr#", text);
		}
		return result;
		
	}
	
	/**
	 * 
	 * @Description TODO
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午10:28:17
	 * @param sequenceName
	 * @return 		参数
	 * @return 		String 返回类型
	 * @throws Exception 
	 */
	public static String createSequenceSQLScript(String sequenceName) throws Exception{
		//判断是否存在这个
		int count = SequencesService.getCountSequencesByName(sequenceName);
		//String sequenceContext = XMLUtils.getSequenceContextByXml();//从xml读取出来不换行
		String path = SqlScriptService.class.getClassLoader().getResource("config/sequence.sql").getPath();
		result = IoUtils.reader(path).replaceAll("#placeholder#", sequenceName);
		if(count== 0){
			//不存在就创建序列，如果用js确定问客户端是否生成当前输入的序列名之后，可以直接生成当前序列名的脚本
			return result;
		}else{
			//存在直接把脚本获取返回给前端
			return result;
		}
	}
	
	
	public static void main(String[] args) throws Exception {
		String tableSQLText = createTableSQLScript();
		System.out.println(tableSQLText);
		//System.out.println(sqlText);
		//保存存脚本数据到本工程,并以.sql后缀文件输出，
		IoUtils.save("output-doc/" + ROOT_PACKAGE.replaceAll("\\.", "/") + "/sqlcript/database.sql",tableSQLText);
		
		
		//String viewText = createViewSQLScript("V_TEST001");
		//IoUtils.save("output-doc/" + ROOT_PACKAGE.replaceAll("\\.", "/") + "/sqlcript/viewText.sql",viewText);
		
		 
		
	}
}
